package student_system.Dao;

import student_system.UnderAccessDBclass.Druid_utils;
import student_system.bean.dep;
import student_system.bean.student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class depdao {
    public ArrayList<dep> dep_select() {

        //1. 得到连接
        Connection connection = null;
        String sql = "select * from dep ";
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        ArrayList<dep> list = new ArrayList<>();

        try {
            connection = Druid_utils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //给占位符赋值
            //preparedStatement.setInt(1, 200301);
            //得到结果集
            set = preparedStatement.executeQuery();
            while (set.next()) {

                String dno = set.getString("dno");
                String dname = set.getString("dname");
                String tel = set.getString("tel");

                list.add(new dep(dno, dname, tel));
                //System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
            }
            System.out.println(list);
        } catch (
                SQLException e) {
            e.printStackTrace();
            //System.out.println("yichang");
        } finally {
            Druid_utils.close(set, preparedStatement, connection);
        }
        //将ResultSet封装到ArrayList
        return list;
    }

    public void adddep(String dno, String dname, String tel) {
        try {
            Connection connection = Druid_utils.getConnection();
            String sql = "insert into dep values (?,?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, dno);
            preparedStatement.setString(2, dname);
            preparedStatement.setString(3, tel);
            preparedStatement.executeUpdate();
            System.out.println("success");

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("error");
        }
    }

    public void depdelete(String dno) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = Druid_utils.getConnection();
            String sql = "DELETE FROM dep WHERE dno=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, dno);
            preparedStatement.executeUpdate();
            System.out.println("success");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("error");
            Druid_utils.close(null, preparedStatement, connection);
        }

    }

    public void alterBydno(String dno, String dname, String tel) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = Druid_utils.getConnection();
            String sql = "update dep set dname=?,tel=? where dno=?";
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, dname);
            preparedStatement.setString(2, tel);
            preparedStatement.setString(3, dno);

            preparedStatement.executeUpdate();
            System.out.println("success");

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            Druid_utils.close(null, preparedStatement, connection);
        }
    }

    public dep selectbydno(String dno) {
        //1. 得到连接
        Connection connection = null;
        String sql = "select * from dep where dno = ?";
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        dep dep = null;
        try {
            connection = Druid_utils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //给占位符赋值
            preparedStatement.setString(1, dno);
            //得到结果集
            set = preparedStatement.executeQuery();
            while (set.next()) {
                dep = new dep(set.getString(1),
                        set.getString(2),
                        set.getString(3));
            }
            //System.out.println(list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            Druid_utils.close(set, preparedStatement, connection);

        }
        return dep;
    }
}